package com.plug.service;

import com.plug.common.consts.Code;
import com.plug.common.jdbc.DBConnectionManager;
import com.plug.common.jdbc.DBConnectionPool;
import com.plug.common.model.JsonResult;
import com.plug.entity.PlugDatasource;
import com.plug.entity.PlugOperationRecord;
import com.plug.repository.PlugOperationRecordMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by slaye on 2016/12/26.
 */
@Service("plugOperationRecordService")
public class PlugOperationRecordServiceImpl implements IPlugOperationRecordService {
    @Autowired
    private IPlugDatasourceService plugDatasourceService;

    private Logger logger = LoggerFactory.getLogger(getClass());
    private DBConnectionManager dbConnectionManager = DBConnectionManager.getInstance();
    @Resource
    private PlugOperationRecordMapper plugOperationRecordMapper;

    public int findTotal() {
        return plugOperationRecordMapper.findTotal();
    }

    public PlugOperationRecord findByID(String id) {
        return plugOperationRecordMapper.selectByPrimaryKey(id);
    }

    public List<PlugOperationRecord> findAll(int offset, int limit) {
        return plugOperationRecordMapper.findAll(offset, limit);
    }

    @Override
    public  List<Object> selectTabData( Integer page, Integer limit, Integer srcId, String tabName, String uid, Integer firstId, Integer endId){
        long src = srcId;
        Connection srcConn = null;
        PlugDatasource srcY = null;
        try {
            srcY = plugDatasourceService.findByID(src);
            srcConn = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(srcY));
        } catch (Exception e) {
            List<Object> result1 = new ArrayList<>();
            result1.add(e);
            return result1;//返回异常数据
        }
        if (srcY==null || srcConn == null){
            return null;
        }
        List<Map<String, Object>> resultData = new ArrayList<>();
        List<Map<String, Object>> resultLie = new ArrayList<>();
        //如果page与limit==0,赋值=1，使下面sql语句始终能够有值
        if (page == 0 || page == null || limit == 0 || limit == null) {
            page = 1;
            limit = 1;
        }
        String sql = null;//查询记录
        String sql2 = null;//判断查询记录的总条数
        logger.debug("------------firstId---{}-------endId--{}----", firstId,endId);
        if(firstId == null ){
            firstId = 0;
        }
        //判断初始位置和起始位置的值，调用相应的sql;
        if((firstId == 0 && endId ==null)){
            sql = "select * from  " + tabName + " order by "+uid+" desc limit " + (page - 1) * limit + "," + limit + "";
            sql2 = "select count(*) from  " + tabName + "";
            logger.debug("---------------{}-------------", sql);
        }
        if(firstId == 0 && endId !=null){
            sql = "select * from  " + tabName + " where "+uid+" <= "+endId+" order by "+uid+" desc limit " + (page - 1) * limit + "," + limit + "";
            sql2 = "select count(*) from  " + tabName + " where "+uid+" <= "+endId+"";
            logger.debug("---------------{}-------------", sql);
        }
        if((firstId != 0 && endId ==null)){
            sql = "select * from  " + tabName + " where "+uid+" >= "+firstId+" order by "+uid+" desc limit " + (page - 1) * limit + "," + limit + "";
            sql2 = "select count(*) from  " + tabName + " where "+uid+" >= "+firstId+"";
            logger.debug("---------------{}-------------", sql);
        }
        if(firstId != 0 && endId !=null){
            sql = "select * from  " + tabName + " where "+uid+" <= "+endId+" and "+uid+" >= "+firstId+" order by "+uid+" desc limit " + (page - 1) * limit + "," + limit + "";
            sql2 = "select count(*) from  " + tabName + " where "+uid+" <= "+endId+" and "+uid+" >= "+firstId+"";
            logger.debug("---------------{}-------------", sql);
        }
        int total = 0;
        try {
            ResultSet rs = null;
            PreparedStatement ps = null;
            ps = srcConn.prepareStatement(sql);
            rs = ps.executeQuery();
            //获取结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //总列数
            int size = rsmd.getColumnCount();
            Map<String, Object> map = new HashMap<>();
            for (int i = 1; i <= size; i++) {
                map = new HashMap<>();
                map.put("field", rsmd.getColumnName(i));
                map.put("title", rsmd.getColumnName(i));
                map.put("align", "center");
                map.put("editor", "text");
                resultLie.add(map);
            }
            //查询数据库表格里面的值，返回到前端表格
            int num = 3;
            while (rs.next()) {
                map = new HashMap<>();
                for (int i = 1; i <= size; i++) {
                    map.put(rsmd.getColumnName(i), rs.getObject(i));
                }
                resultData.add(map);
            }
            //查询总条数total
            ps = srcConn.prepareStatement(sql2);
            rs = ps.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
            ps.close(); rs.close(); rs = null;ps = null;
            List<Object> result = new ArrayList<>();
            result.add(resultLie);
            result.add(resultData);
            result.add(total);
            JsonResult jsonResult = new JsonResult();//源数据集
            jsonResult.setCode(Code.SUCCESS);
            jsonResult.putData("resultData", resultData);
            jsonResult.putData("resultLie", resultLie);
            jsonResult.putData("total", total);
            logger.debug("---------------total{}----------------", total); //记录器
            return result;
        } catch (SQLException e) {
            List<Object> result2 = new ArrayList<>();
            result2.add(e);
            return result2;//返回异常数据
        } finally {
            if (srcConn != null) {
                dbConnectionManager.freeConnection(srcY.getDsName(), srcConn);
            }
        }
    }
}
